/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package insight.toyo.xmlpublisher;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author NSB
 */
public class Main {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        connect();
    }

    private static void connect() {
        Connection connection = null;
        try {
            // Load the JDBC driver
            String driverName = "oracle.jdbc.driver.OracleDriver";
            Class.forName(driverName);

            // Create a connection to the database
            String serverName = "192.168.1.10";
            String portNumber = "1521";
            String sid = "ORCL";
            String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
            String username = "po";
            String password = "po";
            connection = DriverManager.getConnection(url, username, password);
            Statement stmt = connection.createStatement();
            String sql = "select \n"
                    + "XMLELEMENT(\"ORDER\", \n"
                    + "(XMLELEMENT(\"HEADER\", \n"
                    + "XMLELEMENT(\"POH_PROC_TYPE\", 'ORDER'), \n"
                    + "XMLELEMENT(\"PO_NUMBER\", a.PO_NO), \n"
                    + "XMLELEMENT(\"ORDER_TYPE\", 'NOC'), \n"
                    + "XMLELEMENT(\"CLIENT_PO_NUMBER\", ''), "
                    + "XMLELEMENT(\"SUP_CODE\", a.PB_CODE), "
                    + "XMLELEMENT(\"ORIGIN\", 'HO'), "
                    + "XMLELEMENT(\"ISSUED_BY\", a.CHK_BY), "
                    + "XMLELEMENT(\"PO_CURRENCY\", a.CUR_CODE), "
                    + "XMLELEMENT(\"PO_RM_CODE\", ''), "
                    + "XMLELEMENT(\"ORG_ETA_SITE_DATE\", ''), "
                    + "XMLELEMENT(\"BUYER\", ''), "
                    + "XMLELEMENT(\"APPROVED_DATE\", a.AUTH_DATE), "
                    + "XMLELEMENT(\"APPROVED_BY\", a.AUTH_BY), "
                    + "XMLELEMENT(\"NLS\", "
                    + "XMLELEMENT(\"NLS_PROC_TYPE\", 'ORDER'), "
                    + "XMLELEMENT(\"NLS_ID\", '1'), "
                    + "XMLELEMENT(\"SHORT_DESC\", 'PO Desc'), "
                    + "XMLELEMENT(\"DESCRIPTION\", 'Purchase Order Description') "
                    + "), "
                    + "(select "
                    + "XMLAGG( "
                    + "XMLELEMENT(\"LINES\", "
                    + "XMLELEMENT(\"POLI_PROC_TYPE\", 'ORDER'), "
                    + "XMLELEMENT(\"POLI_POS\", b.SR_NO), "
                    + "XMLELEMENT(\"POLI_SUB_POS\", b.SR_NO), "
                    + "XMLELEMENT(\"IDENT_CODE\", b.ITEM_CODE), "
                    + "XMLELEMENT(\"POLI_QTY\", b.QTY), "
                    + "XMLELEMENT(\"POLI_UNIT_PRICE\", b.RATE), "
                    + "XMLELEMENT(\"POLI_CURRENCY\", a.CUR_CODE), "
                    + "XMLELEMENT(\"PRICE_BASE\", ''), "
                    + "XMLELEMENT(\"POLI_UNIT_WEIGHT\", ''), "
                    + "XMLELEMENT(\"POLI_WEIGHT_CODE\", ''), "
                    + "XMLELEMENT(\"POLI_VOLUME\", ''), "
                    + "XMLELEMENT(\"POLI_VOL_CODE\", ''), "
                    + "XMLELEMENT(\"POLI_DIMENSIONS\", b.MATL_SIZE), "
                    + "XMLELEMENT(\"FRT_CODE\", a.PRICE_BASIS), "
                    + "XMLELEMENT(\"FREIGHT_VALUE\", ''), "
                    + "XMLELEMENT(\"MANUFACTURER\", ''), "
                    + "XMLELEMENT(\"PERCENT_US_CONTENT\", ''), "
                    + "XMLELEMENT(\"EXPORT_LICENSE_REQ_IND\", ''), "
                    + "XMLELEMENT(\"EXPORT_LICENSE_NUMBER\", ''), "
                    + "XMLELEMENT(\"TAG_NUMBER\", b.TAG_NO), "
                    + "XMLELEMENT(\"REQ_LINE_ITEM\", c.SR_NO) "
                    + ")  "
                    + ")from PO_D b, PI_D c, ITEM_MAST d where a.PO_NO=b.PO_NO(+) and a.PI_NO=c.PI_NO(+) and b.ITEM_CODE=d.ITEM_CODE  "
                    + ") "
                    + ") "
                    + ")).getClobValue as \"result\" from PO_H a where a.JOB_NO='6254Y' and a.PB_CODE='01756'";
            System.out.println(sql);
            
            ResultSet rs = stmt.executeQuery(sql);

            System.out.println(rs.getClob("results").toString());

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
